-- This script takes it base in the master database.
-- It permits you sign a procedure to bestow it with
-- server-level permissions. If the database is in an 
-- AG, the certificate and login is created on all servers
-- in the AG.
-- This version requires SQL 2012 or later. For a version
-- that runs on SQL 2008, see
-- http://www.sommarskog.se/grantperm.html#managingdbcert
USE master
go
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'PermTest',
        @procname nvarchar(520) = 'ShowSessions'

-- The permissions to grant through the certificate. Set NULL if you only
-- want to remove current signature.
DECLARE @perms nvarchar(4000) = 'VIEW SERVER STATE'

-- Run with debug or not?
DECLARE @debug bit = 1

--============================ END OF SETUP ==========================
-- More local variables.
DECLARE @is_in_ag      bit,
        @sp_executesql nvarchar(150),
        @certname      sysname,
        @username      sysname,
        @subject       nvarchar(4000),
        @pwd           char(39),
        @public_key    varchar(MAX),
        @private_key   varchar(MAX),
        @sql           nvarchar(MAX),
        @server        sysname,
        -- These are comments that we attach to the dynamic SQL to understand
        -- the output better.
        @localcomment  nvarchar(200),
        @dbcomment     nvarchar(200),
        @servercomment nvarchar(200)


SET XACT_ABORT ON

-- A big TRY-CATCH block around everything to abort on first error.
BEGIN TRY

-- First verify that the database exists.
IF db_id(@database) IS NULL
   RAISERROR('Database %s does not exist', 16, 1, @database)

-- Make sure that database name is quoted and appears exactly as in sys.databases.
SELECT @database = quotename(name), 
       @is_in_ag = CASE WHEN group_database_id IS NOT NULL THEN 1 ELSE 0 END
FROM   sys.databases 
WHERE  name = @database

-- We will call sp_executesql a number of times in the target database.
SELECT @sp_executesql = @database + '.sys.sp_executesql'

-- Set up comments for local and the database.
SELECT @localcomment = '/* In (local)/master */', 
       @dbcomment    = '/* In database ' + @database + ' */'  

-- Next we verify that the procedure exists and make sure that
-- we have a normalised quoted name. We need to run a query in the
-- target database.
SELECT @sql = @dbcomment + '
    SELECT @procname = MIN(quotename(s.name) + ''.'' + quotename(o.name))
    FROM   sys.objects o
    JOIN   sys.schemas s ON o.schema_id = s.schema_id
    WHERE  o.object_id = object_id(@procname)'
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@procname nvarchar(260) OUTPUT', @procname OUTPUT

IF @procname IS NULL
   RAISERROR('No procedure with the given name in database %s', 16, 1, @database)

-- Construct name, subject and password for the certificate.
SELECT @certname = 'SIGN ' + @database + '.' + @procname,
       @subject  = 'Signing ' + @database + '.' + @procname + ' for ' + @perms,
       @pwd      = convert(char(36), newid()) + 'Aa0'

-- If a login exists for the cerficiate, we drop it.
IF EXISTS (SELECT *
           FROM   sys.server_principals
           WHERE  name = @certname
             AND  type = 'C')
BEGIN
   SELECT @sql = @localcomment + '
      DROP LOGIN ' + quotename(@certname)
   IF @debug = 1 PRINT @sql
   EXEC (@sql)
END

-- And drop the certificate itself.
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
BEGIN
   SELECT @sql = @localcomment + '
      DROP CERTIFICATE ' + quotename(@certname)
   IF @debug = 1 PRINT @sql
   EXEC(@sql)
END

-- In the target database, we must remove the signature from the procedure,
-- so that we can drop the certificate.
SELECT @sql = @dbcomment + '
   IF EXISTS (SELECT *
              FROM   sys.crypt_properties cp
              JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
              WHERE  cp.major_id = object_id(@procname)
                AND  c.name = @certname)
      DROP SIGNATURE FROM ' + @procname + ' BY CERTIFICATE ' + quotename(@certname)
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname sysname, @procname nvarchar(260)',
                    @certname, @procname

-- No user should have been created from the cert, but if so, we drop it.
-- Since this may been performed by some else, we cannot trust the username
-- to be the same as the certificate name.
SELECT @sql = @dbcomment + '
   SELECT @username = NULL
   SELECT @username = dp.name
   FROM   sys.database_principals dp
   JOIN   sys.certificates c ON dp.sid = c.sid
   WHERE  c.name = @certname'
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname  sysname, @username sysname OUTPUT',
                          @certname, @username OUTPUT

IF @username IS NOT NULL
BEGIN
   SELECT @sql = @dbcomment + '
      DROP USER ' + quotename(@username)
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql
END

-- And here goes the old cert.
SELECT @sql = @dbcomment + '
   IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
      DROP CERTIFICATE ' + quotename(@certname)
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname  sysname', @certname

IF @perms IS NOT NULL
BEGIN
   -- Now we start to (re)create things. First create the certificate in master.
   SELECT @sql = @localcomment + '
      CREATE CERTIFICATE ' + quotename(@certname) + '
      ENCRYPTION BY PASSWORD = ''' + @pwd + '''
      WITH SUBJECT = ''' + @subject + ''''
   IF @debug = 1 PRINT @sql
   EXEC(@sql)

   -- And the login for the certificate.
   SELECT @sql = @localcomment + '
      CREATE LOGIN ' + quotename(@certname) + ' FROM CERTIFICATE ' + quotename(@certname)
   IF @debug = 1 PRINT @sql
   EXEC(@sql)

   -- Grant the permissions.
   SELECT @sql = @localcomment + '
      GRANT ' + @perms + ' TO ' + quotename(@certname)
   IF @debug = 1 PRINT @sql
   EXEC(@sql)

   -- Retrieve the keys for the certificate as hex-strings.
   SELECT @public_key  = convert(varchar(MAX), 
                            certencoded(cert_id(quotename(@certname))), 1),
          @private_key = convert(varchar(MAX),
              certprivatekey(cert_id(quotename(@certname)), @pwd, @pwd), 1)

   -- Copy the certificate to the user database. 
   SELECT @sql = @dbcomment + '
      CREATE CERTIFICATE ' + quotename(@certname) + '
      FROM BINARY = ' + @public_key + '
      WITH PRIVATE KEY (BINARY = ' + @private_key + ',
           ENCRYPTION BY PASSWORD = ''' + @pwd + ''',
           DECRYPTION BY PASSWORD = ''' + @pwd + ''')'
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- We can now sign the procedure.
   SELECT @sql = @dbcomment + '
       ADD SIGNATURE TO ' + @procname + ' BY CERTIFICATE ' +
          quotename(@certname) + ' WITH PASSWORD = ''' + @pwd + ''''
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- Drop the private key of the cert from the databases.
   SELECT @sql = @localcomment + '
      ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY'
   IF @debug = 1 PRINT @sql
   EXEC (@sql)

   SELECT @sql = @dbcomment + '
      ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY'
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql
END
   
-- If the database is part of an availability group, the certificate needs to be propagated 
-- to the master databases on the other nodes as well.
IF @is_in_ag = 1
BEGIN
   -- Set up a cursor over all the other nodes.
   DECLARE ag_cur CURSOR STATIC LOCAL FOR
      SELECT replica_server_name
      FROM   sys.availability_replicas
      WHERE  replica_server_name <> convert(sysname, serverproperty('ServerName'))

   OPEN ag_cur

   WHILE 1 = 1
   BEGIN
      FETCH ag_cur INTO @server
      IF @@fetch_status <> 0
         BREAK

      -- This is a string that we put above the debug output of all SQL commands we run on the
      -- remote server.
      SELECT @servercomment = '/* On server "' + @server + '" */' 

      -- Set up a temporary linked server to this node. As we will use sp_executesql,
      -- we need RPC out to be enabled.
      EXEC sp_addlinkedserver 'TEMP$SERVER', '', 'SQLOLEDB', @server
      EXEC sp_serveroption 'TEMP$SERVER', 'RPC out', 'true'

      -- If a login exists for the cerficiate on the other node, we drop it
      SELECT @sql = @servercomment + '
         IF EXISTS (SELECT *
                    FROM   sys.server_principals
                    WHERE  name = @certname
                      AND  type = ''C'') 
            DROP LOGIN ' + quotename(@certname)
      IF @debug = 1 PRINT @sql
      EXEC TEMP$SERVER.master.sys.sp_executesql @sql, N'@certname sysname', @certname

      -- And drop the certificate itself.
      SELECT @sql = @servercomment + ' 
         IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
            DROP CERTIFICATE ' + quotename(@certname)
      IF @debug = 1 PRINT @sql
      EXEC TEMP$SERVER.master.sys.sp_executesql @sql, N'@certname sysname', @certname

      -- Only create new certs etc, if there are any permissions to grant.
      IF @perms IS NOT NULL
      BEGIN
      
         -- Create the certificate on thise server, using the binary variables.
         SELECT @sql = @servercomment + ' 
            CREATE CERTIFICATE ' + quotename(@certname) + '
            FROM BINARY = ' + @public_key + '
            WITH PRIVATE KEY (BINARY = ' + @private_key + ',
                  ENCRYPTION BY PASSWORD = ''' + @pwd + ''',
                  DECRYPTION BY PASSWORD = ''' + @pwd + ''')'
         IF @debug = 1 PRINT @sql
         EXEC TEMP$SERVER.master.sys.sp_executesql @sql

         -- The login.
         SELECT @sql = @servercomment + '
            CREATE LOGIN ' + quotename(@certname) + ' FROM CERTIFICATE ' + quotename(@certname)
         IF @debug = 1 PRINT @sql
         EXEC TEMP$SERVER.master.sys.sp_executesql @sql

         -- Grant the permissions.
         SELECT @sql = @servercomment + '
            GRANT ' + @perms + ' TO ' + quotename(@certname)
         IF @debug = 1 PRINT @sql
         EXEC TEMP$SERVER.master.sys.sp_executesql @sql

         -- Drop the private key.
         SELECT @sql = @servercomment + '
            ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY'
         IF @debug = 1 PRINT @sql
         EXEC TEMP$SERVER.master.sys.sp_executesql @sql
      END

      -- Now that alls is done, drop the temporary server.
      EXEC sp_dropserver 'TEMP$SERVER'
   END

   -- Get rid of the cursor.
   DEALLOCATE ag_cur
END
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
